<?php
App::uses('AppModel', 'Model');
/**
 * User Model
 */
class Report extends AppModel {


	public $useDbConfig = 'axiaCon';
	
	public $useTable = false;


	//VALIDATION
	
	function jobWOldReferrals()
    {

        $jobWOldReferrals = "select tem.JobId, tem.JobName, tem.RequestVersion 'Ver', tem.Description 
       ,datediff (day, isnull(tem.PeriodEndDate,31/12/2099),tem.endDate) 'Days old'
       ,tem.OfficeName
       ,u.Username
       ,convert (varchar, tem.startDate, 103) 'Start Date'
      ,convert (varchar, tem.endDate, 103) 'End Date'
      ,convert (varchar, tem.periodstartDate, 103) 'Referal Start'
      ,convert (varchar, tem.periodendDate, 103) 'Referal end'
      ,cast (refcreatedon as smalldatetime) 'Ref Created'
      ,refCreatedBy
      ,cast (refModifiedOn as smalldatetime) 'Ref Modified'
      ,refmodifiedBy
      ,cast (tem.CreatedOn as smalldatetime) 'Version Created'
      ,tem.CreatedBy 'By'
      ,cast (tem.ModifiedOn as smalldatetime) 'Version Modified'
      ,tem.ModifiedBy 'By'
     
   from 
(SELECT j.JobId, j.JobName, r.RequestVersion , r.Description 
      ,o.OfficeName, o.ManagedByUserId
      ,r.startDate
      ,r.endDate
      ,r.CreatedOn 
      ,r.CreatedBy 
      ,r.ModifiedOn 
      ,r.ModifiedBy
      ,ref.periodstartDate
      ,ref.periodendDate
      ,ref.CreatedOn refcreatedon
      ,ref.CreatedBy refcreatedby
      ,ref.ModifiedOn refmodifiedon
      ,ref.modifiedBy refmodifiedby
    
 --     ,ref.*
 --     ,r.*
 --     ,j.*
  FROM [AxiaCon].[dbo].[app_Referral] ref, [AxiaCon].[dbo].[app_Request] r, [AxiaCon].[dbo].[app_Job] j,
       [AxiaCon].[dbo].[app_Office] o
  where ref.RequestId = r.RequestId
  and ref.JobId = J.jobid
  and j.OfficeId = o.OfficeId
  and r.RequestStatus in ('SCHEDULE ALLOCATION')
  and r.EndDate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
  and ref.PeriodEndDate < r.EndDate
  and ref.DeletedFlag = 'N'
  and r.DeletedFlag = 'N'
  and j.DeletedFlag = 'N'
  
  UNION ALL
  
  SELECT j1.JobId, j1.JobName, r1.RequestVersion 
      ,r1.Description, o1.OfficeName, o1.ManagedByUserId
      ,r1.startDate
      ,r1.endDate
      ,r1.CreatedOn 
      ,r1.CreatedBy 
      ,r1.ModifiedOn 
      ,r1.ModifiedBy 
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      
  FROM [AxiaCon].[dbo].[app_Request] r1, [AxiaCon].[dbo].[app_Job] j1, [AxiaCon].[dbo].[app_Office] o1
  where   r1.RequestStatus = 'SCHEDULE ALLOCATION'
  and j1.JobId = r1.JobId
  and j1.OfficeId = o1.OfficeId
  and r1.RequestId not in (select RequestId from [AxiaCon].[dbo].[app_Referral] where RequestId is not null)
  and r1.EndDate > CURRENT_TIMESTAMP
  and r1.DeletedFlag = 'N'
  and j1.DeletedFlag = 'N'
  and j1.JobId not in (1704, 1705, 1339, 1826)
  ) as tem
 left outer join
 (select userid, Username from [AxiaCon].[dbo].[cor_User]) u
     on tem.ManagedByUserId = u.UserId 
  order by datediff (day, isnull(tem.PeriodEndDate,31/12/2099),tem.endDate) desc,
           tem.endDate, tem.startdate, tem.ModifiedOn desc
 

		";

        return $this->query($jobWOldReferrals);
    }
			
	
	//The Associations below have been created with all possible keys, those that are not needed can be removed

/**
 * belongsTo associations
 *
 * @var array
 */

}
